#!/usr/bin/perl -w

use strict;
use warnings;
use DBI;
use Date::Calc;
use DateTime;
use MIME::Lite;
use Net::SMTP;

my $dt = DateTime->now->subtract(days => 7);
my $startdate = sprintf "%4d-%02d-%02d", $dt->year,$dt->month,$dt->day;
my ($sec,$min,$hour,$mday,$mon,$year,$wday,
    $yday,$isdst)=localtime(time);
my $date = sprintf "%4d-%02d-%02d", $year+1900,$mon+1,$mday;
my $time = sprintf "%02d:%02d:%02d", $hour,$min,$sec;
my $week = Date::Calc::Week_Number($year+1900, $mon+1, $mday);

# Database settings
my $username = 'metria';
my $password = '';
my $database = 'metria';
my $hostname = 'localhost';

my $dbh = DBI->connect("dbi:mysql:database=$database;" .
		       "host=$hostname;port=3306", $username, $password);

my $file="/home/myuser/reports/$date.html"; 
if (-e $file) 
{
    unlink($file);
} 
open(my $FILE, ">>$file") or die "Cannot open $file: $!";
print $FILE  <<ENDHTML;
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 
<html lang='en' xml:lang='en' xmlns='http://www.w3.org/1999/xhtml'> 
<head><title>Veckorapport $date</title> 
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />  
<style type="text/css"> 
body {font-family: Verdana,Arial,sans-serif;}
body,p,blockquote,li,th,td {font-size: 10pt;}
h1 { font-size: 1.4em; }
h2 { font-size: 1.1em; margin-top: 2em; margin-bottom: 0.5em; }
h3 { font-size: 1.0em; font-style: italic; font-weight: normal; margin-top: 2em; margin-bottom: 0.5em; }
code {font-size: 1.1em;}
p,blockquote,pre,ul,ol,dl,table {margin-top: 0px;margin-bottom: 1em;}
table.MyTable {border-spacing: 0px;border-collapse: collapse;border-top: 4px solid #EA9A1C;border-bottom: 1px solid #EA9A1C;}
table.MyTable th {text-align: left;font-weight: normal;padding: 0.2em 0.5em;border-top: 1px dotted #EA9A1C;}
table.MyTable td {text-align: left;border-top: 1px dotted #EA9A1C;padding: 0.2em 0.5em;}
table.MyTable thead th {text-align: center;border-bottom: 1px solid #EA9A1C;}
table.MyTable th.Corner {text-align: left;}
</style></head>
<body>
<table> 
<tr><td><h1>Weekly report $week</h1></td></tr>
</table>
ENDHTML

# As is the report is divided by day time
&mytable($FILE, $dbh, $date, 0, '07:00', '16:00');
&mytable($FILE, $dbh, $date, 0, '16:00', '20:00');
&mytable($FILE, $dbh, $date, 1, '20:00', '07:00');

print $FILE "<p>The statistics is collected between $startdate and $date.</p>";
print $FILE "</body></html>\n";
close($FILE); 
&mysendmail();

#
# Sends the report as email using the SMTP protocol
#
sub mysendmail {
    use MIME::Lite;
    use Net::SMTP;

    my $from_address = 'from@domain.se';
    my $to_address = 'to@domain.se';
    my $mail_host = 'smtpserver.domain.se';
    my $subject = "Weekly report w$week";
    my $message_body;# = "See attachment.";
    my $filename = "$date.html";

    open FILE, $file  or die $!;
    binmode FILE;
    my ($buf, $data, $n);
    while (($n = read FILE, $data, 4) != 0) {
	$message_body .= $data;
    }
    close(FILE);

    my $msg = MIME::Lite->new (
	From => $from_address,
	To => $to_address,
	Subject => $subject,
	Type =>'multipart/mixed'
	) or die "Error creating multipart container: $!\n";
    
    $msg->attach (
	Type => 'TEXT/HTML',
	Data => $message_body
	) or die "Error adding the text message part: $!\n";

    $msg->attach (
	Type => 'text/html',
	Path => $file,
	Filename => $filename,
	Disposition => 'attachment'
	) or die "Error adding $file: $!\n";

    MIME::Lite->send('smtp', $mail_host, Timeout=>60);
    $msg->send;
}

#
# Gets and prints statistics for certain dates and times
#
sub mytable {
    my $LFILE = shift;
    my $dbh = shift;
    my $ldate = shift;
    my $nightspan = shift;
    my $starttime = shift;
    my $endtime = shift;
    my ($ok1,$ok2,$ok3,$ok4,$avg1,$avg2,$avg3,$avg4,$max1,$max2,$max3,$max4,$min1,$min2,$min3,$min4,$t1,$t2,$t3,$t4) = 0;
    my $SQL;
    my $Select;
    my $Row;
    my $tspan;
    my $dspan = " date between ('$ldate' - 7) and '$ldate'";

    print $FILE  <<ENDHTML;
<h3>$starttime-$endtime</h3>
<table cellspacing='0' class="MyTable"> 
<thead><tr><th class="Corner">M&auml;tning</th> 
<th>Test 1</th><th>Test 2</th><th>Test 3</th><th>Test 4</th></tr></thead> 
<tbody> 
ENDHTML

    if ($nightspan) {
	$tspan = " and (time between '$starttime' and '24:00' or time between '00:00' and '$endtime')";
    }
    else {
	$tspan = " and time between '$starttime' and '$endtime' ";
    }   

    $SQL= "select count(time1) as num_ok,cast(avg( time1 ) as unsigned) as avg,max(time1) as max,min(time1) as min from metria_statistics where $dspan $tspan and status1 = 0 and time1 > 0";
    # print "Debug: $SQL";
    $Select = $dbh->prepare($SQL);
    $Select->execute();
    $Row=$Select->fetchrow_hashref;    
    $avg1 = $Row->{avg};
    $max1 = $Row->{max};
    $min1 = $Row->{min};
    $ok1 =  $Row->{num_ok};

    $SQL= "select count(time1) as num_failed from metria_statistics where $dspan $tspan and status1 != 0";
#    print "Debug: $SQL";
    $Select = $dbh->prepare($SQL);
    $Select->execute();
    $Row=$Select->fetchrow_hashref;
    if ($ok1 > 0) {
	$t1 = sprintf("%.2f", ( 100 - ($Row->{num_failed} / $ok1)*100));
    }
    elsif ($Row->{num_failed} > 0) {
	$t1 = 100;
    }
    else {
	$t1 = "";
    }

$SQL= "select count(time2) as num_ok,cast(avg( time2 ) as unsigned) as avg,max(time2) as max,min(time2) as min from metria_statistics where date between ('$ldate' - 7) and '$ldate' $tspan and status2 = 0  and time2 > 0";
#    print "Debug: $SQL";
    $Select = $dbh->prepare($SQL);
    $Select->execute();
    $Row=$Select->fetchrow_hashref;
    $avg2 = $Row->{avg};
    $max2 = $Row->{max};
    $min2 = $Row->{min};
    $ok2 =  $Row->{num_ok};

    $SQL= "select count(time2) as num_failed from metria_statistics where $dspan $tspan and status2 != 0";
#    print "Debug: $SQL";
    $Select = $dbh->prepare($SQL);
    $Select->execute();
    $Row=$Select->fetchrow_hashref;
    if ($ok2 > 0) {
        $t2 = sprintf("%.2f", ( 100 - ($Row->{num_failed} / $ok2)*100));
    }
    elsif ($Row->{num_failed} > 0) {
        $t2 = 100;
    }
    else {
        $t2 = "";
    }


$SQL= "select count(time3) as num_ok,cast(avg( time3 ) as unsigned) as avg,max(time3) as max,min(time3) as min from metria_statistics where date between ('$ldate' - 7) and '$ldate' $tspan and status3 = 0  and time3 > 0";
#    print "Debug: $SQL";
    $Select = $dbh->prepare($SQL);
    $Select->execute();
    $Row=$Select->fetchrow_hashref;
    $avg3 = $Row->{avg};
    $max3 = $Row->{max};
    $min3 = $Row->{min};
    $ok3 =  $Row->{num_ok};

$SQL= "select count(time3) as num_failed from metria_statistics where $dspan $tspan and status3 != 0";
#    print "Debug: $SQL";
    $Select = $dbh->prepare($SQL);
    $Select->execute();
    $Row=$Select->fetchrow_hashref;
    if ($ok3 > 0) {
        $t3 = sprintf("%.2f", ( 100 - ($Row->{num_failed} / $ok3)*100));
    }
    elsif ($Row->{num_failed} > 0) {
        $t3 = 100;
    }
    else {
        $t3 = "";
    }

    $SQL= "select count(time4) as num_ok,cast(avg( time4 ) as unsigned) as avg,max(time4) as max,min(time4) as min from metria_statistics where $dspan $tspan and status4 = 0  and time4 > 0";
#    print "Debug: $SQL";
    $Select = $dbh->prepare($SQL);
    $Select->execute();
    $Row=$Select->fetchrow_hashref;
    $avg4 = $Row->{avg};
    $max4 = $Row->{max};
    $min4 = $Row->{min};
    $ok4 =  $Row->{num_ok};

    $SQL= "select count(time4) as num_failed from metria_statistics where $dspan $tspan and status4 != 0";
#    print "Debug: $SQL";
    $Select = $dbh->prepare($SQL);
    $Select->execute();
    $Row=$Select->fetchrow_hashref;
    if ($ok4 > 0) {
        $t4 = sprintf("%.2f", ( 100 - ($Row->{num_failed} / $ok4)*100));
    }
    elsif ($Row->{num_failed} > 0) {
        $t4 = 100;
    }
    else {
        $t4 = "";
    }

    print $LFILE "<tr><th>Average response(ms):</th><td>$avg1</td><td>$avg2</td><td>$avg3</td><td>$avg4</td></tr>\n";
    print $LFILE "<tr><th>Max response(ms):</th><td>$max1</td><td>$max2</td><td>$max3</td><td>$max4</td></tr>\n";
    print $LFILE "<tr><th>Min response(ms):</th><td>$min1</td><td>$min2</td><td>$min3</td><td>$min4</td></tr>\n";
    print $LFILE "<tr><th>Availability(%):</th><td>$t1</td><td>$t2</td><td>$t3</td><td>$t4</td></tr>\n";
    print $LFILE '</tbody></table>';
}

